Skip Headers

Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2) Part Number B14239-04 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# C Data Type and DDL Support on a Logical Standby Database

When setting up a logical standby database, you must ensure the logical standby database can maintain the data types and tables in your primary database. This appendix lists the various database objects, storage types, and PL/SQL supplied packages that are supported and unsupported by logical standby databases. It contains the following topics:

- Data Type Considerations

- Storage Type Considerations

- PL/SQL Supplied Packages Considerations

- Unsupported Tables, Sequences, and Views

- Skipped SQL Statements on a Logical Standby Database

- DDL Statements Supported by a Logical Standby Database

## C.1 Data Type Considerations

The following sections list the supported and unsupported database objects:

- Supported Data Types in a Logical Standby Database

- Unsupported Data Types in a Logical Standby Database

### C.1.1 Supported Data Types in a Logical Standby Databas e

Logical standby databases support the following data types:

`BINARY_DOUBLE`
`BINARY_FLOAT`
`BLOB`
`CHAR`
`CLOB` and `NCLOB`
`DATE`
`INTERVAL YEAR TO MONTH`
`INTERVAL DAY TO SECOND`
`LONG`
`LONG RAW`
`NCHAR`
`NUMBER`
`NVARCHAR2`
`RAW`
`TIMESTAMP`
`TIMESTAMP WITH LOCAL TIMEZONE`
`TIMESTAMP WITH TIMEZONE`
`VARCHAR2 and VARCHAR`

Note: SQL Apply support for the following data types has compatibility requirements on the primary database: Multibyte `CLOB` support (requires primary database to run at a compatibility of 10.1 or higher). IOT support without `LOB`s and Overflows (requires primary database to run at a compatibility of 10.1 or higher); IOT support with `LOB` and Overflow (requires primary database to run at a compatibility of 10.2 or higher)

### C.1.2 Unsupported Data Types in a Logical Standby Databas e

Logical standby databases do not support the following data types:

`BFILE`
Collections (including `VARRAYS` and nested tables)
Encrypted columns
Multimedia data types (including Spatial, Image, and Context)
`ROWID`, `UROWID`
User-defined types
XMLType

## C.2 Storage Type Considerations

The following sections list the supported and unsupported storage types:

- Support Storage Types

- Unsupported Storage Type

### C.2.1 Support Storage Type s

Logical standby databases support the following storage types:

Cluster tables (including index clusters and heap clusters)
Index-organized tables (partitioned and nonpartitioned, including overflow segments)
Heap-organized tables (partitioned and nonpartitioned

### C.2.2 Unsupported Storage Type

Logical standby databases do not support the segment compression storage type.

## C.3 PL/SQL Supplied Packages Considerations

The following sections list the supported and unsupported PL/SQL supplied packages:

- Supported PL/SQL Supplied Packages

- Unsupported PL/SQL Supplied Packages

See Also: Oracle Database PL/SQL Packages and Types Reference for more information about Oracle PL/SQL supplied packages

### C.3.1 Supported PL/SQL Supplied Package s

Oracle PL/SQL supplied packages that do not modify system metadata or user data leave no footprint in the archived redo log files, and hence are safe to use on the primary database. Examples of such packages are `DBMS_OUTPUT`, `DBMS_RANDOM`, `DBMS_PIPE`, `DBMS_DESCRIBE`, `DBMS_OBFUSCATION_TOOLKIT`, `DBMS_TRACE`, and `DBMS_METADATA`.

Oracle PL/SQL supplied packages that do not modify system metadata but may modify user data are supported by SQL Apply, as long as the modified data belongs to the supported data types listed in Section C.1.1. Examples of such packages are `DBMS_LOB`, `DBMS_SQL`, and `DBMS_TRANSACTION`.

### C.3.2 Unsupported PL/SQL Supplied Packages

Oracle PL/SQL supplied packages that modify system metadata typically are not supported by SQL Apply, and therefore their effects are not visible on the logical standby database. Examples of such packages are `DBMS_JAVA`, `DBMS_REGISTRY`, `DBMS_ALERT`, `DBMS_SPACE_ADMIN`, `DBMS_REFRESH`, `DBMS_REDEFINITION`, `DBMS_SCHEDULER`, `and DBMS_AQ`.

Specific support for `DBMS_JOB` has been provided. Job execution is suspended on a logical standby database and jobs cannot be scheduled directly on the standby database. However, jobs submitted on the primary database are replicated in the standby database. In the event of a switchover or failover, jobs scheduled on the original primary database will automatically begin running on the new primary database.

## C.4 Unsupported Tables, Sequences, and View s

It is important to identify unsupported database objects on the primary database before you create a logical standby database. This is because changes made to unsupported data types, tables, sequences, or views on the primary database will be automatically skipped by SQL Apply on the logical standby database. Moreover, no error message will be returned.

Some schemas that ship with the Oracle database are skipped by SQL Apply. To determine exactly which schemas will be skipped, query the `DBA_LOGSTDBY_SKIP` view.

    SELECT OWNER FROM DBA_LOGSTDBY_SKIP WHERE STATEMENT_OPT = 'INTERNAL SCHEMA';

To determine if the primary database contains unsupported objects, query the `DBA_LOGSTDBY_UNSUPPORTED` view. See Chapter 16, "Views Relevant to Oracle Data Guard" for more information about views.

For example, use the following query on the primary database to list the schema and table names of primary database tables that are not supported by logical standby databases:

    SQL> SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED
      2> ORDER BY OWNER,TABLE_NAME;

    OWNER        TABLE_NAME
    -----------  --------------------------
    HR           COUNTRIES
    OE           ORDERS
    OE           CUSTOMERS
    OE           WAREHOUSES

To view the column names and data types for one of the tables listed in the previous query, use a `SELECT` statement similar to the following:

    SQL> SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED
      2> WHERE OWNER='OE' AND TABLE_NAME = 'CUSTOMERS';

    COLUMN_NAME                      DATA_TYPE
    -------------------------------  -------------------
    CUST_ADDRESS                     CUST_ADDRESS_TYP
    PHONE_NUMBERS                    PHONE_LIST_TYP
    CUST_GEO_LOCATION                SDO_GEOMETRY

If the primary database contains unsupported tables, SQL Apply automatically excludes these tables when applying redo data to the logical standby database.

Note: If you determine that the critical tables in your primary database will not be supported on a logical standby database, then you might want to consider using a physical standby database. Physical standby databases do not have any such data type restrictions.

## C.5 Skipped SQL Statements on a Logical Standby Database

By default, the following SQ L statements are automatically skipped by SQL Apply:

`ALTER DATABASE`
`ALTER MATERIALIZED VIEW`
`ALTER MATERIALIZED VIEW LOG`
`ALTER SESSION`
`ALTER SYSTEM`
`CREATE CONTROL FILE`
`CREATE DATABASE`
`CREATE DATABASE LINK`
`CREATE PFILE FROM SPFILE`
`CREATE MATERIALIZED VIEW`
`CREATE MATERIALIZED VIEW LOG`
`CREATE SCHEMA AUTHORIZATION`
`CREATE SPFILE FROM PFILE`
`DROP DATABASE LINK`
`DROP MATERIALIZED VIEW`
`DROP MATERIALIZED VIEW LOG`
`EXPLAIN`
`LOCK TABLE`
`SET CONSTRAINTS`
`SET ROLE`
`SET TRANSACTION`

All other SQL statements executed on the primary database are applied to the logical standby database.

## C.6 DDL Statements Supported by a Logical Standby Database

The following tables list the supported values for the `stmt` parameter of the `DBMS_LOGSTDBY.SKIP` procedure and the statement options for skipping SQL DDL statements:

- Table C-1, "Values for stmt Parameter of the DBMS_LOGSTDBY.SKIP procedure"

- Table C-2, "Statement Options for Skipping SQL DDL Statements"

See Also: Oracle Database PL/SQL Packages and Types Reference for complete information about the `DBMS_LOGSTDBY` package and Section 9.4.4, "Setting up a Skip Handler for a DDL Statement"

Table C-1 lists the supported values for the `stmt` parameter of the `DBMS_LOGSTDBY.SKIP` procedure. The left column of the table lists the keywords that may be used to identify the set of SQL statements to the right of the keyword. Any of the SQL statements in the right column, however, are also valid values. Note that keywords are generally defined by database object.

Table C-1 Values for stmt Parameter of the DBMS_LOGSTDBY.SKIP procedure

Keyword | Associated SQL Statements
--- | ---
`DML` | Includes DML statements on a table (for example: `INSERT`, `UPDATE`, and `DELETE`)
`CLUSTER` |
    CREATE CLUSTER
    AUDIT CLUSTER
    DROP CLUSTER
    TRUNCATE CLUSTER
`CONTEXT` |
    CREATE CONTEXT
    DROP CONTEXT
`DATABASE LINK` |
    CREATE DATABASE LINK
    DROP DATABASE LINK
`DIMENSION` |
    CREATE DIMENSION
    ALTER DIMENSION
    DROP DIMENSION
`DIRECTORY` |
    CREATE DIRECTORY
    DROP DIRECTORY
`INDEX` |
    CREATE INDEX
    ALTER INDEX
    DROP INDEX
`NON_SCHEMA_DDL` | All DDL that does not pertain to a particular schema
`PROCEDURE` Foot 1 |
    CREATE FUNCTION
    CREATE LIBRARY
    CREATE PACKAGE
    CREATE PACKAGE BODY
    CREATE PROCEDURE
    DROP FUNCTION
    DROP LIBRARY
    DROP PACKAGE
    DROP PROCEDURE
`PROFILE` |
    CREATE PROFILE
    ALTER PROFILE
    DROP PROFILE
`PUBLIC DATABASE LINK` |
    CREATE PUBLIC DATABASE LINK
    DROP PUBLIC DATABASE LINK
`PUBLIC SYNONYM` |
    CREATE PUBLIC SYNONYM
    DROP PUBLIC SYNONYM
`ROLE` |
    CREATE ROLE
    ALTER ROLE
    DROP ROLE
    SET ROLE
`ROLLBACK STATEMENT` |
    CREATE ROLLBACK SEGMENT
    ALTER ROLLBACK SEGMENT
    DROP ROLLBACK SEGMENT
`SCHEMA_DDL` | All DDL statements that create, modify, or drop schema objects (for example: tables, indexes, and columns)
`SEQUENCE` |
    CREATE SEQUENCE
    DROP SEQUENCE
`SESSION` |
    Log-ons
`SYNONYM` |
    CREATE SYNONYM
    DROP SYNONYM
`SYSTEM AUDIT` |
    AUDIT SQL_statements
    NOAUDIT SQL_statements
`SYSTEM GRANT` |
    GRANT system_privileges_and_roles
    REVOKE system_privileges_and_roles
`TABLE` |
    CREATE TABLE
    DROP TABLE
    TRUNCATE TABLE
`TABLESPACE` |
    CREATE TABLESPACE
    DROP TABLESPACE
    TRUNCATE TABLESPACE
`TRIGGER` |
    CREATE TRIGGER
    ALTER TRIGGER with ENABLE and DISABLE clauses
    DROP TRIGGER
    ALTER TABLE with ENABLE ALL TRIGGERS clause
    ALTER TABLE with DISABLE ALL TRIGGERS clause
`TYPE` |
    CREATE TYPE
    CREATE TYPE BODY
    ALTER TYPE
    DROP TYPE
    DROP TYPE BODY
`USER` |
    CREATE USER
    ALTER USER
    DROP USER
`VIEW` |
    CREATE VIEW
    DROP VIEW

Footnote 1 Java schema objects (sources, classes, and resources) are considered the same as procedure for purposes of skipping (ignoring) SQL statements.

Table C-2 lists the statement options for skipping SQL DDL statements.

Table C-2 Statement Options for Skipping SQL DDL Statements

Statement Option | SQL Statements and Operations
--- | ---
`ALTER SEQUENCE` | `ALTER SEQUENCE`
`ALTER TABLE` | `ALTER TABLE`
`COMMENT TABLE` | `COMMENT ON TABLE` `table, view, materialized view` `COMMENT ON COLUMN` `table.column, view.column, materialized_view.column`
`DELETE TABLE` | `DELETE FROM` `table, view`
`EXECUTE PROCEDURE` | `CALL` Execution of any procedure or function or access to any variable, library, or cursor inside a package.
`GRANT DIRECTORY` | `GRANT` `privilege` `ON` `directory` `REVOKE` `privilege` `ON` `directory`
`GRANT PROCEDURE` | `GRANT` `privilege` `ON` `procedure, function, package` `REVOKE` `privilege` `ON` `procedure, function, package`
`GRANT SEQUENCE` | `GRANT` `privilege` `ON` sequence `REVOKE` `privilege` `ON` sequence
`GRANT TABLE` | `GRANT` `privilege` `ON` `table, view, materialized view` `REVOKE` `privilege` `ON` `table, view, materialized view`
`GRANT TYPE` | `GRANT` `privilege` `ON TYPE` `REVOKE` `privilege` `ON TYPE`
`INSERT TABLE` | `INSERT INTO` `table, view`
`LOCK TABLE` | `LOCK TABLE` `table, view`
`SELECT SEQUENCE` | Any statement containing `sequence`.`CURRVAL` or
`SELECT TABLE` | `SELECT FROM` `table, view, materialized view` `REVOKE` `privilege` `ON` `table, view, materialized view`
`UPDATE TABLE` | `UPDATE` `table, view`

See Also: The following sections that provide usage examples of the `SKIP` and `UNSKIP` options: Section 9.4.3, "Using DBMS_LOGSTDBY.SKIP to Prevent Changes to Specific Schema Objects" Section 9.4.4, "Setting up a Skip Handler for a DDL Statement" Section 9.4.5, "Modifying a Logical Standby Database" Section 9.4.6, "Adding or Re-Creating Tables On a Logical Standby Database"